from django.db import models
from tools import mysqlHelper
from tools import mssqlHelper


class monitor(models.Model):
    def get_db_size(self):
        sql ='''
        SELECT
            CONVERT(VARCHAR(10),querydate,120) dt
          , cast(MIN([free_size(GB)]) as varchar(10)) min_free_siz
          , cast(MAX([free_size(GB)]) as varchar(10)) max_free_siz
        FROM dw.dbo.fact_md_db_size_detail
        WHERE drive_name = 'D'
          AND server_ip = '192.168.10.88'
          AND querydate >= GETDATE() -30
        GROUP BY CONVERT(VARCHAR(10),querydate,120)
        ,drive_name
        ORDER BY 1
        '''
        fetchall= mssqlHelper.getdata(sql,False)
        return fetchall

    def get_tablesize(self):
        sql ='''
        SELECT
             DISTINCT TOP 20  A.DBNAME,A.TABLENAME
            ,A.ROWS
            ,CAST(A.TOTAL_SIZE AS DECIMAL(20,2)) today_size
            ,CAST(C.TOTAL_SIZE AS DECIMAL(20,2)) lastday_size
            ,CAST((A.TOTAL_SIZE - C.TOTAL_SIZE) AS DECIMAL(20,2)) incr
        FROM dw.dbo.ods_md_tablesize A WITH(NOLOCK)
        LEFT JOIN dw.dbo.ods_md_tablesize C WITH(NOLOCK) ON A.DBNAME = C.DBNAME AND A.TABLENAME = C.TABLENAME
              AND CONVERT(VARCHAR(13),c.INS_DATE,120) = CONVERT(VARCHAR(13),DATEADD(DD,-1,GETDATE()),120)
        WHERE CONVERT(VARCHAR(13),A.INS_DATE,120) = CONVERT(VARCHAR(13),GETDATE(),120)
        ORDER BY 6 DESC
        '''
        fetchall = mssqlHelper.getdata(sql)
        return fetchall

    def get_user_funnel(self):
        sql ='''
           	SELECT '100' AS access_uv,'30' AS goshop_uv,CAST(CAST(ROUND((buy_user_cnt+0.0)/goshop_uv,2)*100 AS INT) AS VARCHAR(100)) AS buy_user_cnt
            FROM (
                SELECT ds,goshop_uv
                FROM dw.DM.incr_d_tra_goshop_stype
                WHERE DS = CONVERT(VARCHAR(10),GETDATE() -1,120)
                AND ds_type = 'day'
                AND IS_APP = -1
                AND product_type = -1
            ) t1
            JOIN (
                SELECT ds,SUM(buy_user_cnt) buy_user_cnt
                FROM dw.DM.incr_d_odr_pre_order_stype
                WHERE DS = CONVERT(VARCHAR(10),GETDATE() -1,120)
                AND ds_type = 'day'
                GROUP BY ds
            ) t2 ON t1.ds = t2.ds
        '''
        fetchall = mssqlHelper.getdata(sql,False)
        return fetchall